#!/bin/bash
APP=edu

if [ -n "$2" ] ;then
   do_date=$2
else 
   echo "请传入日期参数"
   exit
fi

dws_trade_user_course_order_nd="
insert overwrite table ${APP}.dws_trade_user_course_order_nd partition(dt='$do_date')
select
    toi.user_id,
    toi.course_id,
    course_name,
    subject_id,
    subject_name,
    category_id,
    category_name,
    order_count_7d,
    order_amount_7d,
    order_count_30d,
    order_amount_30d
from (
    select
        user_id,
        course_id,
        sum(if(dt>=date_add('$do_date',-6), order_count_1d, 0)) as order_count_7d,
        sum(if(dt>=date_add('$do_date',-6), order_amount_1d, 0)) as order_amount_7d,
        sum(order_count_1d) as order_count_30d,
        sum(order_amount_1d) as order_amount_30d
    from ${APP}.dws_trade_user_course_order_1d
    where dt>=date_add('$do_date',-29) and order_amount_1d is not null
    group by user_id,course_id
) as toi join (
    select
        DISTINCT(id) as course_id,
        subject_id,
        course_name,
        subject_name,
        category_id,
        category_name
    from ${APP}.dim_course_info_full
) as ci on toi.course_id=ci.course_id;
"
dws_trade_province_order_nd="
insert overwrite table ${APP}.dws_trade_province_order_nd partition(dt='$do_date')
select
    province_id,
    province_name,
    area_code,
    iso_code,
    iso_3166_2,
    sum(if(dt>=date_add('$do_date',-6),order_user_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_count_1d,0)),
    sum(if(dt>=date_add('$do_date',-6),order_amount_1d,0)),
    sum(order_user_1d),
    sum(order_count_1d),
    sum(order_amount_1d)
from ${APP}.dws_trade_province_order_1d
where dt>=date_add('$do_date',-29)
and dt<='$do_date'
group by province_id,province_name,area_code,iso_code,iso_3166_2;
"

case $1 in
    "dws_trade_user_course_order_nd" )
        hive -e "$dws_trade_user_course_order_nd"
    ;;
    "dws_trade_province_order_nd" )
        hive -e "$dws_trade_province_order_nd"
    ;; 
    "all" )
        hive -e "$dws_trade_user_course_order_nd$dws_trade_province_order_nd"
    ;;
esac
